package net.bingosoft.common.util;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import jxl.Cell;
import jxl.CellType;
import jxl.CellView;
import jxl.DateCell;
import jxl.NumberCell;
import jxl.Range;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import net.bingosoft.common.BeanHelper;
import net.bingosoft.common.ObjectRegistry;
import net.bingosoft.common.data.BaseDao;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.htmlparser.Node;
import org.htmlparser.NodeFilter;
import org.htmlparser.Parser;
import org.htmlparser.filters.TagNameFilter;
import org.htmlparser.tags.TableColumn;
import org.htmlparser.tags.TableRow;
import org.htmlparser.tags.TableTag;
import org.htmlparser.util.NodeList;
import org.htmlparser.util.ParserException;

import bingo.persister.Persister;
import bingo.persister.dao.JdbcDao;

import com.jenkov.mrpersister.impl.ResultSetProcessorBase;
import com.jenkov.mrpersister.itf.PersistenceException;
import com.jenkov.mrpersister.itf.command.IDaos;

/**
 * 2007-8-21 下午04:30:17
 * Author:yangdongyu(yangdy@bingosoft.net)
 * ReadMe:操作excel的工具类,方便excel的导出
 * ChangeLog:Ver1.0
 */
public class ExcelUtils {
	private static final Log log = LogFactory.getLog(ExcelUtils.class);
	private WritableCellFormat dataFormat;
	private WritableCellFormat headerFormat;
	private int headerHeigth = 0;
	private ExcelUtils(){
		//util class
	}

	/**
	 * 创建一个工作簿
	 * @param outputStream
	 * @return
	 * @throws IOException
	 */
	public WritableWorkbook createWorkbook(OutputStream outputStream) throws IOException{
		return Workbook.createWorkbook(outputStream);
	}
	/**
	 * 创建一个可写的工作表
	 * @param workbook
	 * @param sheetNum
	 * @return
	 */
	public WritableSheet createSheet(WritableWorkbook workbook,int sheetNum){
		WritableSheet sheet = workbook.createSheet("Sheet " + sheetNum , sheetNum);
		return sheet;
	}

	/**
	 * 向Excel写入一行
	 * @param sheet
	 * @param row
	 * @param rowData
	 * @throws RowsExceededException
	 * @throws WriteException
	 * @throws SQLException
	 */
	private void buildRow(WritableSheet sheet,final int row,TableColumn[] rowData) throws RowsExceededException, WriteException, SQLException{
		for (int col = 0 ; col < rowData.length ; col++){
			Node firstChild = rowData[col].childAt(0);
			String text = (firstChild == null)? "":firstChild.getText();
			text = (text == null) ? "":text.trim();
			text = text.equals("&nbsp;") || text.equals("")?" ":text;
			log.debug("当前单元格 (" + X(col) +"," + Y(row)+") 内容: " + text);
			int []colRow = getRightLocation(sheet,col,row);
			int rowspan = BeanHelper.getInt(rowData[col].getAttribute("rowspan"),1) - 1;
			int colspan = BeanHelper.getInt(rowData[col].getAttribute("colspan"),1) - 1;
			if(row == 0 && col == 0){
				headerHeigth = rowspan ;
			}
			Label label = new Label(colRow[0],colRow[1],text,getCellFormat(row <= headerHeigth));
			if(rowspan > 0 || colspan > 0){
				Range range = sheet.mergeCells(colRow[0], colRow[1], colRow[0] + colspan , colRow[1] + rowspan);
				log.debug("合并单元格: " + range);
			}
			sheet.addCell(label);
			sheet.setColumnView(col, getCellView());
		}
	}
	/**
	 * 获取CellView ,控制单元格的宽度
	 * @return
	 * @throws WriteException
	 */
	private CellView getCellView() throws WriteException{
		CellView cv = new CellView();
		cv.setAutosize(true);
		return cv;
	}
	/**
	 * 由于单元格的合并,某些单元格的位置需要调整,该方法用来获取单元格的正确位置
	 * @param sheet
	 * @param col
	 * @param row
	 * @return
	 */
	private int[] getRightLocation(WritableSheet sheet,int col,int row){
		Range[] range = sheet.getMergedCells();
		int colOld = col;
		int rowOld = row;
		for(Range r : range){
			Cell tl = r.getTopLeft();
			Cell rb = r.getBottomRight();
			int xStart = tl.getColumn();
			int yStart = tl.getRow();
			int xEnd = rb.getColumn();
			int yEnd = rb.getRow();
			if(col >= xStart && col <= xEnd && row >= yStart && row <= yEnd){
				log.debug("当前单元格 (" + X(col) +"," + Y(row)+") 落入合并单元格内部,合并范围:" + r);
//				if(xEnd - xStart == 0 && yEnd - yStart > 0){
				log.debug("单元格右移...");
				//垂直合并
				col ++;
				log.debug("单元格右移之后的位置为 (" + X(col) +"," + Y(row)+")");
				String content = sheet.getCell(col, row).getContents();
				while(!content.equals("")){
					log.debug("调整位置后单元格的内容不为空,单元格内容为:" + content + ",继续右移...");
					content = sheet.getCell( ++ col,row).getContents();
					log.debug("单元格右移之后的位置为 (" + X(col) +"," + Y(row)+"),当前单元格的内容为: " + content);
				}
				log.debug("调整完毕,当前位置 (" + X(col) +"," + Y(row)+")");
//				}
//				if(xEnd - xStart > 0 && yEnd - yStart == 0){
//				log.debug("水平合并,单元格下移...");
//				//水平合并
//				row ++;
//				log.debug("单元格下移之后的位置为 (" + X(col) +"," + row+")");
//				String content = sheet.getCell(col, row).getContents();
//				while(!content.equals("")){
//				log.debug("调整位置后单元格的内容不为空,单元格内容为:" + content + ",继续下移...");
//				content = sheet.getCell(col,++row).getContents();
//				log.debug("单元格下移之后的位置为 (" + X(col) +"," + row+"),当前单元格的内容为:" + content);
//				}
//				log.debug("水平调整完毕,当前位置 (" + X(col) +"," + row+")");
//				}
			}
		}
		String content = sheet.getCell(col, row).getContents();
		while(!content.equals("")){//没有落入合并单元格内部,但当前单元格已有内容
			log.debug("当前单元格的内容不为空,单元格内容为:" + content + ",单元格右移...");
			content = sheet.getCell( ++ col,row).getContents();
			log.debug("单元格右移之后的位置为 (" + X(col) +"," + row+"),当前单元格的内容为: " + content);
		}
		log.debug("原始坐标: (" + X(colOld) +"," + Y(rowOld) + ")经调整后最终坐标为(" + X(col) +"," + Y(row)+")");
		return new int[]{col,row};
	}
	/**
	 * 获取X坐标的位置
	 * @param x
	 * @return
	 */
	private String X(int x){
		String location = "";
		if(x+'A' > 'Z'){
			location +='A';
			x -= 'Z'-'A'+1;
		}
		return location + (char)('A'+x);
	}
	/**
	 * 获取Y坐标的值
	 * @param y
	 * @return
	 */
	private int Y(int y){
		return y+1;
	}
	/**
	 * 获取数据单元格的格式
	 * @return
	 * @throws WriteException
	 */
	public WritableCellFormat getCellFormat(boolean isHeader) throws WriteException{
		if(isHeader && headerFormat != null){
			return headerFormat;
		}
		if(!isHeader && dataFormat != null){
			return dataFormat;
		}
		WritableFont font = null;
		if(isHeader){
			font = new WritableFont(WritableFont.ARIAL,WritableFont.DEFAULT_POINT_SIZE, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK); 
		}else{
			font = new WritableFont(WritableFont.ARIAL, WritableFont.DEFAULT_POINT_SIZE, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
		}
		WritableCellFormat format = new WritableCellFormat (font); 
		format.setBorder(Border.ALL,BorderLineStyle.THIN);
		format.setAlignment(Alignment.CENTRE);
//		format.setShrinkToFit(true);
		format.setVerticalAlignment(VerticalAlignment.CENTRE);
		if(isHeader){
			format.setBackground(Colour.GRAY_25);
			format.setBorder(Border.ALL, BorderLineStyle.THIN,Colour.GRAY_50);
			headerFormat = format;
		}else{
//			format.setBackground(Colour.GRAY_50);
//			format.setBorder(Border.ALL, BorderLineStyle.THIN,Colour.GRAY_50);
			dataFormat = format;
		}
		return format;
	}


	/**
	 * 创建Excel文档
	 * @param rest
	 * @param outputStream
	 */
	private void buildExcel(TableColumn[][] bodyData,OutputStream outputStream){
		WritableWorkbook workbook = null;
		try {			
			workbook = Workbook.createWorkbook(outputStream);	
			WritableSheet sheet= createSheet(workbook,1);
			for(int rowIndex = 0 ; rowIndex <  bodyData.length ;rowIndex++){
				buildRow(sheet, rowIndex, bodyData[rowIndex]);
			}
			workbook.write();
		} catch (Exception e) {
			log.error("创建单元格出错",e);
		}finally{
			try {
				outputStream.flush();
				workbook.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 创建一个工作表
	 * @param bodyData
	 * @param outputStream
	 * @throws FileNotFoundException 
	 */
	private void buildExcel(TableColumn[][] bodyData,String file) throws FileNotFoundException{
		buildExcel(bodyData, new FileOutputStream(file));
	}
	/**
	 * 读取Excel的内容,返回一个二维数组列表,每个二维数组代表一个工作表(Sheet)
	 * @param xlsFile excel文件路径
	 * @return List<String[][]> 二维数组列表,每个二维数组代表一个工作表(Sheet)
	 * @throws Exception
	 */
	public static List<String[][]> readExcel(String xlsFile) throws Exception {
		Workbook workbook = null;
		List<String[][]> resultList = new ArrayList<String[][]>();
		try{
			WorkbookSettings settings = new WorkbookSettings();
			settings.setEncoding("GBK");
			workbook = Workbook.getWorkbook(new File(xlsFile),settings);
			Sheet []sheets = workbook.getSheets();
			//便利多个工作表,每个工作表返回一个二维数组
			for(Sheet sheet : sheets){
				String [][]contents = null;
				//获取总的行数和列数
				int totalRows = sheet.getRows();
				int totalColumns = sheet.getColumns();
				contents = new String [totalRows][totalColumns]; 
				Cell cell = null;
				for (int row = 0; row < totalRows; row++) {
					for (int col = 0; col < totalColumns; col++) {
						// 注意,这里的两个参数,第一个是表示列的,第二才表示行
						cell = sheet.getCell(col, row);
						// 要根据单元格的类型分别做处理,否则格式化过的内容可能会不正确
						if (cell.getType() == CellType.NUMBER) {
							NumberCell numCell = (NumberCell) cell;
							NumberFormat numberFormat = numCell.getNumberFormat();
							contents[row][col] = String.valueOf(numberFormat.format(numCell.getValue()));
						} else if (cell.getType() == CellType.DATE) {
							DateCell dateCell = (DateCell) cell;
							DateFormat dateFormat = dateCell.getDateFormat();
							contents[row][col] = String.valueOf(dateFormat.format(dateCell.getDate()));
						} else {
							contents[row][col] = cell.getContents();
						}
//						log.debug(contents[row][col] + "\t");
					}
//					log.debug("\n");
				}
				resultList.add(contents);
			}
		}catch(Exception ex){
			log.debug("读取Excel内容时发生错误...",ex);
		}finally{
			try{
				if(workbook != null)
					workbook.close();	
			}catch(Exception ex){
				log.debug("关闭工作簿发生异常...",ex);
			}
		}
		return resultList;

	}
	/**
	 * 导出Excel文件
	 * @param exportFileName
	 * @param dataBody
	 * @param response
	 * @throws IOException
	 * @throws WriteException 
	 * @throws ParserException 
	 * @throws RowsExceededException 
	 */
	public static void exportExcel(String exportFileName,String tableHTML,HttpServletResponse response) throws IOException, RowsExceededException, ParserException, WriteException{
		exportFileName = FileHelper.encodeFileName(exportFileName);
		response.setContentType("application/vnd.ms-excel");
		response.setHeader("Content-Disposition", "attachment;filename=\"" + exportFileName + "\"");
		response.setHeader("Content-Transfer-Encoding","binary");
		response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
		response.setHeader("Pragma", "public");
		response.setDateHeader("Expires", (System.currentTimeMillis() + 1000));
		response.flushBuffer();
		OutputStream os = response.getOutputStream();
		doExportExcel(tableHTML,os);
		os.flush();
	}
	/**
	 * 导出Excel文件
	 * @param bodyData
	 * @param os
	 * @throws IOException 
	 * @throws WriteException 
	 * @throws ParserException 
	 * @throws RowsExceededException 
	 */
	private static void doExportExcel(String tableHTML,OutputStream os) throws RowsExceededException, ParserException, WriteException, IOException{
		ExcelUtils instance = new ExcelUtils();
		TableColumn[][] data = instance.getTableData(tableHTML);
		instance.buildExcel(data, os);
	}
	/**
	 * 导出Excel文件
	 * @param bodyData
	 * @param os
	 * @throws IOException 
	 * @throws WriteException 
	 * @throws ParserException 
	 * @throws RowsExceededException 
	 */
	public static void doExportExcel(String tableHTML,String xlsFileName) throws RowsExceededException, ParserException, WriteException, IOException{
		ExcelUtils instance = new ExcelUtils();
		TableColumn[][] data = instance.getTableData(tableHTML);
		instance.buildExcel(data, xlsFileName);
	}

	/**
	 * 解析table字符串中的数据
	 * @param tableHTML 要解析的table 字符串
	 * @return
	 * @throws ParserException
	 * @throws IOException
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	private TableColumn[][] getTableData(String tableHTML) throws ParserException, IOException, RowsExceededException, WriteException {
		Parser parser = Parser.createParser(tableHTML, "UTF-8");
		NodeFilter filter = new TagNameFilter("Table");
		NodeList tables = parser.extractAllNodesThatMatch(filter);
		TableTag tabletag = (TableTag) tables.elementAt(0);
		int rowCount = tabletag.getRowCount();
		TableColumn result [][] = new TableColumn[rowCount][];
		for(int i = 0 ;i < tabletag.getRowCount();i++){
			TableRow row = tabletag.getRow(i);
			TableColumn[] cols = row.getColumns();
			result[i] = new TableColumn[cols.length];
			for(int j  = 0 ;j < cols.length;j ++ ){
				result[i][j]  = cols[j];
			}
		}
		return result;
	}
	/**
	 * 导出ResultSet 至Excel 表格
	 * @param filePath 导出文件路径
	 * @param titles 标题行
	 * @param overwrite 如果指定文件已存在,是否覆盖
	 * @param exportSQL 导出Excel的sql语句
	 * @param datasourceKey 执行excel的数据源的键值
	 * @param sqlParams sql查询参数
	 * @return
	 * @throws PersistenceException
	 * @throws IOException
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	public static String exportExcel(String filePath,final String[]titles,boolean overwrite,String exportSQL,String datasourceKey,Object ...sqlParams) throws PersistenceException, IOException, RowsExceededException, WriteException{
		if(new File(filePath).exists() && !overwrite){
			return filePath;
		}
		final ExcelUtils instance = new ExcelUtils();
		//创建工作簿
		String excelFile = FileHelper.createFile(filePath);
		FileOutputStream fos = new FileOutputStream(excelFile);
		final WritableWorkbook workbook = instance.createWorkbook(fos);	
		final WritableSheet sheet= instance.createSheet(workbook, 1);
		if(titles != null){
			//写标题行
			for(int i = 0 ; i < titles.length;i++){
				sheet.addCell(new Label(i,0,titles[i],instance.getCellFormat(true)));
			}
		}
		
		try{
			Persister p ;
			if(StringUtils.isEmpty(datasourceKey)){
				p = ObjectRegistry.getBaseDao().getPersister();
			}else{
				p = ObjectRegistry.getBaseDao().getPersister(datasourceKey);
			}
			JdbcDao dao = p.getJdbcDao();
			dao.read(exportSQL, sqlParams, new ResultSetProcessorBase(){
				private int columnCount =0;
				private int rowIndex = 0;
				@Override
				public void init(ResultSet result) throws SQLException, PersistenceException {
					//获取行数
					ResultSetMetaData rsmd =result.getMetaData();
					this.columnCount = rsmd.getColumnCount();
					if(titles != null && titles.length != columnCount){
						log.warn("标题列数和ResultSet查询出的列数不同...");
					}
					if(titles == null || titles.length == 0){
						//写标题行
						for(int i = 1 ; i <= this.columnCount;i++){
							try {
								sheet.addCell(new Label(i-1,0,rsmd.getColumnLabel(i),instance.getCellFormat(true)));
							} catch (Exception e) {
							} 
						}
					}
				}
				@Override
				public void process(ResultSet result, IDaos daos) throws SQLException{
					int columnIndex = 0;
					rowIndex++;
					while(columnIndex < columnCount){
						try {
							sheet.addCell(new Label(columnIndex++,rowIndex,result.getString(columnIndex),instance.getCellFormat(false)));
						} catch (Exception e) {
							throw new RuntimeException("读取ResultSet内容向工作簿写内容时发生错误...",e);
						} 
					}					
				}
				@Override
				public Object getResult() throws PersistenceException {
					try {
						workbook.write();
					} catch (IOException e) {
						throw new RuntimeException("读取ResultSet内容向工作簿写内容时发生错误...",e);
					}
					return null;
				}
			});

		}finally{
			try{
				if(workbook != null)
					workbook.close();	
			}catch(Exception ex){
				log.debug("关闭工作簿发生异常...",ex);
			}
		}
		return filePath;
	}
	/**
	 * 用sql导出excel到输出流
	 * @param tiltes excel的标题行
	 * @param exportSQL 导出excel 的查询语句
	 * @param os 输出流
	 * @throws PersistenceException
	 * @throws IOException
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	public static void exportExcel(final String[]tiltes,String exportSQL,OutputStream os) throws PersistenceException, IOException, RowsExceededException, WriteException{

		final ExcelUtils instance = new ExcelUtils();
		File tempFile = File.createTempFile("temp", ".xls");
		//创建工作簿
		OutputStream _os = new FileOutputStream(tempFile);
		final BufferedOutputStream bos = new BufferedOutputStream(_os,1024*1024);
		final WritableWorkbook workbook = instance.createWorkbook(bos);	
		final WritableSheet sheet= instance.createSheet(workbook, 1);
		CellFormat headerFormat = instance.getCellFormat(false);
		//写标题行
		for(int i = 0 ; i < tiltes.length;i++){
			sheet.addCell(new Label(i,0,tiltes[i],headerFormat));
		}
		FileInputStream fis = null;
		System.out.println("准备查询:"+System.currentTimeMillis());
		try{
			Persister p = ObjectRegistry.getBean("baseDao", BaseDao.class).getPersister();
			JdbcDao dao = p.getJdbcDao();
			dao.read(exportSQL, new ResultSetProcessorBase(){
				private CellFormat bodyFormat = instance.getCellFormat(false);
				private int columnCount =0;
				private int rowIndex = 0;
				@Override
				public void init(ResultSet result) throws SQLException, PersistenceException {
					System.out.println("查询完毕:"+System.currentTimeMillis());
					//获取行数
					this.columnCount = result.getMetaData().getColumnCount();
					if(tiltes != null && tiltes.length != columnCount){
						log.warn("标题列数和ResultSet查询出的列数不同...");
					}
				}
				@Override
				public void process(ResultSet result, IDaos daos) throws SQLException{
					int columnIndex = 0;
					rowIndex++;
					try {
						while(columnIndex < columnCount){
							sheet.addCell(new Label(columnIndex++,rowIndex,result.getString(columnIndex),bodyFormat));
						}
						if(rowIndex %1000 ==0){
							workbook.write();
						}
						System.out.println(rowIndex);
					} catch (Exception e) {
						throw new RuntimeException("读取ResultSet内容向工作簿写内容时发生错误...",e);
					} 
				}
				@Override
				public Object getResult() throws PersistenceException {

					try {
						workbook.write();
					} catch (IOException e) {
						throw new RuntimeException("读取ResultSet内容向工作簿写内容时发生错误...",e);
					}
					return null;
				}
			});			
		}finally{
			try{
				if(workbook != null){
					workbook.close();
				}
				if(bos !=null){
					bos.close();
				}
				if(fis != null){
					fis.close();
				}
			}catch(Exception ex){
				log.debug("关闭工作簿发生异常...",ex);
			}

		}
	}
	/**
	 * 通过sql导出Excel
	 * @param exportFileName 导出文件的名称
	 * @param titles 调出Excel表格的标题
	 * @param sql 导出Excel的SQL
	 * @param response
	 * @throws IOException
	 * @throws RowsExceededException
	 * @throws ParserException
	 * @throws WriteException
	 * @throws PersistenceException
	 */
	public static void exportExcelBySQL(String exportFileName,String[] titles,String sql,HttpServletResponse response) throws IOException, RowsExceededException, ParserException, WriteException, PersistenceException{
		exportFileName = FileHelper.encodeFileName(exportFileName);
		response.setContentType("application/vnd.ms-excel");
		response.setHeader("Content-Disposition", "attachment;filename=\"" + exportFileName + "\"");
		response.setHeader("Content-Transfer-Encoding","binary");
		response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
		response.setHeader("Pragma", "public");
		response.setDateHeader("Expires", (System.currentTimeMillis() + 1000));
		response.flushBuffer();
		OutputStream os = response.getOutputStream();
		exportExcel(titles,sql,os);
		os.flush();
	}
	public static void main(String[] args) throws Exception {
		//doExportExcel(FileHelper.readStreamContent(ExcelUtils.class.getResourceAsStream("table.txt"),"UTF-8"),"test2.xls");
		readExcel("c:/test.xls");
	}
}
